# coding = utf-8
import datetime
import json
from io import BytesIO

from django.db import transaction
from django.db.models import Q, F, Sum, Value, Case, When, FloatField
from django.db.models.functions import Coalesce, ExtractYear, ExtractMonth
from django.forms import model_to_dict
from django.http import HttpResponse
from django.utils.encoding import escape_uri_path

from rest_framework.generics import ListAPIView

from rest_framework.views import APIView

from rest_framework import status
from rest_framework.decorators import action
from rest_framework.response import Response
from rest_framework.viewsets import ModelViewSet

import pandas as pd
from openpyxl.utils import get_column_letter
from openpyxl.styles import Alignment, Font
from openpyxl.styles import Border, Side

from base.models import CustomSupplier, Company
from finance.api.utils import increase_account_amount
from finance.models import FundBill, Prepayment
from sale.api.utils import decrease_sale_order_amounts
from sale.models import SaleOrder, SaleReceipt, SaleOrderDetail, SaleReceiptDetail
from store.api.utils import sale_increase_goods_stocks
from .serializers import SaleOrderSerializer, SaleReceiptSerializer, SaleOrderDetailSerializer, \
    SaleReceiptDetailSerializer, SaleOrderListSerializer, SaleOrderCustomerSerializer


class SaleOrderCustomerAPIView(APIView):
    """借出单客户列表"""
    # authentication_classes = ()
    permission_classes = ()

    def get(self, request, *args, **kwargs):
        # 借出, 未归还
        queryset = SaleOrder.objects.filter(flag=3, status=2).values("customer_id", customer_name=F("customer__name")).order_by('customer_id').distinct()
        return Response(list(queryset))


class SaleOrderListAPIView(ListAPIView):
    """出库单列表"""
    permission_classes = ()
    queryset = SaleOrder.objects.filter(Q(flag=0) | Q(flag=1)).exclude(status=1)
    serializer_class = SaleOrderListSerializer
    pagination_class = None

    def get_queryset(self):
        queryset = super().get_queryset()

        name = self.request.query_params.get("name", None)
        customer_id = self.request.query_params.get("customer", None)

        if name:
            queryset = queryset.filter(code__contains=name)

        if customer_id:
            queryset = queryset.filter(customer_id=int(customer_id))

        return queryset


class SaleOrderDetailListAPIView(ListAPIView):
    """出库单明细列表"""
    permission_classes = ()
    queryset = SaleOrderDetail.objects.filter(sale_order__flag=3).exclude(nums=F('return_nums'))
    serializer_class = SaleOrderDetailSerializer
    pagination_class = None

    def get_queryset(self):
        queryset = super().get_queryset()

        name = self.request.query_params.get("name", None)
        customer_id = self.request.query_params.get("customer", None)

        if name:
            queryset = queryset.filter(sale_order__code__contains=name)

        if customer_id:
            queryset = queryset.filter(sale_order__customer_id=int(customer_id))

        return queryset


class SaleOrderDetailViewSet(ModelViewSet):
    """销售出库明细"""
    permission_classes = ()
    queryset = SaleOrderDetail.objects.all()
    serializer_class = SaleOrderDetailSerializer
    pagination_class = None

    def get_queryset(self):
        queryset = super().get_queryset()
        return queryset

    @action(methods=['delete'], detail=False)
    def batch_delete(self, request, *args, **kwargs):
        select_ids = request.data.get("select_ids", None)
        if not select_ids:
            msg = {"status_code": status.HTTP_404_NOT_FOUND, "message": "删除失败!"}
            return Response(data=msg, status=status.HTTP_404_NOT_FOUND)
        with transaction.atomic():
            save_id = transaction.savepoint()
            try:
                self.update_goods_stock(select_ids)
                self.serializer_class.Meta.model.objects.filter(id__in=select_ids).delete()
            except Exception as e:
                print(e)
                transaction.savepoint_rollback(save_id)

            transaction.savepoint_commit(save_id)

        msg = {"status_code": status.HTTP_204_NO_CONTENT, "message": "删除成功!"}
        return Response(data=msg, status=status.HTTP_204_NO_CONTENT)

    def update_goods_stock(self, ids):
        objs = self.serializer_class.Meta.model.objects.filter(id__in=ids)
        # 删除商品，增加库存
        sale_increase_goods_stocks(objs)


class SaleOrderViewSet(ModelViewSet):
    """销售单"""
    permission_classes = ()
    queryset = SaleOrder.objects.all()
    serializer_class = SaleOrderSerializer
    pagination_class = None

    def get_queryset(self):
        queryset = super().get_queryset()

        flag = self.request.query_params.get("flag", None)
        code = self.request.query_params.get("code", None)
        search_date = self.request.query_params.getlist("search_date[]", None)
        customer_id = self.request.query_params.get("customer", None)
        saler_id = self.request.query_params.get("saler", None)

        if flag:
            queryset = queryset.filter(flag=int(flag))

        if code:
            queryset = queryset.filter(code__contains=code)

        if search_date and len(search_date) == 2:
            queryset = queryset.filter(order_time__range=(search_date[0], search_date[1]))

        if customer_id:
            queryset = queryset.filter(customer_id=int(customer_id))

        if saler_id:
            queryset = queryset.filter(saler_id=int(saler_id))

        return queryset

    def destroy(self, request, *args, **kwargs):
        instance = self.get_object()
        with transaction.atomic():
            save_id = transaction.savepoint()
            try:
                sale_increase_goods_stocks(instance.details.all())
                if instance.imprest:
                    increase_account_amount(instance.account.id, (-1) * instance.imp_amount, instance.customer_id)

                self.perform_destroy(instance)

            except Exception as e:
                print(e)
                transaction.savepoint_rollback(save_id)

            transaction.savepoint_commit(save_id)

        return Response(status=status.HTTP_204_NO_CONTENT)

    @action(methods=['delete'], detail=False)
    def batch_delete(self, request, *args, **kwargs):
        select_ids = request.data.get("select_ids", None)
        if not select_ids:
            msg = {"status_code": status.HTTP_404_NOT_FOUND, "message": "删除失败!"}
            return Response(data=msg, status=status.HTTP_404_NOT_FOUND)

        with transaction.atomic():
            save_id = transaction.savepoint()
            try:
                self.update_goods_stock(select_ids)
                self.serializer_class.Meta.model.objects.filter(id__in=select_ids).delete()
            except Exception as e:
                print(e)
                transaction.savepoint_rollback(save_id)

            transaction.savepoint_commit(save_id)

        self.serializer_class.Meta.model.objects.filter(id__in=select_ids).delete()
        msg = {"status_code": status.HTTP_204_NO_CONTENT, "message": "删除成功!"}
        return Response(data=msg, status=status.HTTP_204_NO_CONTENT)

    def update_goods_stock(self, ids):
        sales = self.serializer_class.Meta.model.objects.filter(id__in=ids)
        for sale in sales:
            if sale.imprest:
                increase_account_amount(sale.account.id, (-1) * sale.imp_amount, sale.customer_id)
            sale_increase_goods_stocks(sale.details.all())


class SaleReceiptViewSet(ModelViewSet):
    """销售收款单"""
    permission_classes = ()
    queryset = SaleReceipt.objects.all()
    serializer_class = SaleReceiptSerializer
    pagination_class = None

    def get_queryset(self):
        queryset = super().get_queryset()

        name = self.request.query_params.get("name", None)
        search_date = self.request.query_params.getlist("search_date[]", None)
        customer_id = self.request.query_params.get("customer", None)
        user_id = self.request.query_params.get("user", None)
        oper_id = self.request.query_params.get("oper", None)

        if name:
            queryset = queryset.filter(code__contains=name)

        if search_date and len(search_date) == 2:
            queryset = queryset.filter(recv_time__range=(search_date[0], search_date[1]))

        if customer_id:
            queryset = queryset.filter(customer_id=int(customer_id))

        if user_id:
            queryset = queryset.filter(user_id=int(user_id))

        if oper_id:
            queryset = queryset.filter(oper_id=int(oper_id))

        return queryset

    def destroy(self, request, *args, **kwargs):
        instance = self.get_object()
        with transaction.atomic():
            save_id = transaction.savepoint()
            try:
                if not instance.sale_order_code:
                    increase_account_amount(instance.account_id, -instance.actual_amount, instance.customer_id)
                    decrease_sale_order_amounts(instance.details.all())
                self.perform_destroy(instance)
            except Exception as e:
                print(e)
                transaction.savepoint_rollback(save_id)

            transaction.savepoint_commit(save_id)

        return Response(status=status.HTTP_204_NO_CONTENT)

    @action(methods=['delete'], detail=False)
    def batch_delete(self, request, *args, **kwargs):
        select_ids = request.data.get("select_ids", None)
        if not select_ids:
            msg = {"status_code": status.HTTP_404_NOT_FOUND, "message": "删除失败!"}
            return Response(data=msg, status=status.HTTP_404_NOT_FOUND)

        with transaction.atomic():
            save_id = transaction.savepoint()
            try:
                self.update_sale_order(select_ids)
                self.serializer_class.Meta.model.objects.filter(id__in=select_ids).delete()
            except Exception as e:
                print(e)
                transaction.savepoint_rollback(save_id)

            transaction.savepoint_commit(save_id)

        msg = {"status_code": status.HTTP_204_NO_CONTENT, "message": "删除成功!"}
        return Response(data=msg, status=status.HTTP_204_NO_CONTENT)

    def update_sale_order(self, ids):
        receipts = self.serializer_class.Meta.model.objects.filter(id__in=ids)
        for receipt in receipts:
            if not receipt.sale_order_code:
                increase_account_amount(receipt.account_id, -receipt.actual_amount, receipt.customer_id)
                decrease_sale_order_amounts(receipt.details.all())


class SaleReceiptDetailViewSet(ModelViewSet):
    """收款单明细"""
    permission_classes = ()
    queryset = SaleReceiptDetail.objects.all()
    serializer_class = SaleReceiptDetailSerializer
    pagination_class = None

    def get_queryset(self):
        queryset = super().get_queryset()
        return queryset

    @action(methods=['delete'], detail=False)
    def batch_delete(self, request, *args, **kwargs):
        select_ids = request.data.get("select_ids", None)
        if not select_ids:
            msg = {"status_code": status.HTTP_404_NOT_FOUND, "message": "删除失败!"}
            return Response(data=msg, status=status.HTTP_404_NOT_FOUND)
        with transaction.atomic():
            save_id = transaction.savepoint()
            try:
                self.update_sale_order(select_ids)
                self.serializer_class.Meta.model.objects.filter(id__in=select_ids).delete()
            except Exception as e:
                print(e)
                transaction.savepoint_rollback(save_id)

            transaction.savepoint_commit(save_id)

        msg = {"status_code": status.HTTP_204_NO_CONTENT, "message": "删除成功!"}
        return Response(data=msg, status=status.HTTP_204_NO_CONTENT)

    def update_sale_order(self, ids):
        objs = self.serializer_class.Meta.model.objects.filter(id__in=ids)
        for obj in objs:
            if obj.category == 1:
                self.remove_prepay_amount(obj)
        # 减出库订单
        decrease_sale_order_amounts(objs)

    def remove_prepay_amount(self, obj):
        code = obj.receipt_order.code + '预收款'
        Prepayment.objects.filter(code=code).delete()


class SaleBillAPIView(APIView):
    """销售出库单对账表"""
    permission_classes = ()

    def get(self, request, *args, **kwargs):
        customer_id = self.request.query_params.get("customer")
        search_date = self.request.query_params.getlist('search_date[]')
        goods_id = self.request.query_params.get('goods')
        export = request.query_params.get("export")

        customer = None
        orders = SaleOrder.objects.filter(Q(flag=0) | Q(flag=1))
        queryset = SaleOrderDetail.objects.filter(Q(sale_order__flag=0) | Q(sale_order__flag=1))

        if goods_id:
            queryset = queryset.filter(goods_id=int(goods_id))

        if customer_id:
            queryset = queryset.filter(sale_order__customer_id=int(customer_id))
            orders = orders.filter(customer_id=int(customer_id))
            # 客户
            customer = CustomSupplier.objects.filter(id=int(customer_id)).first()

        if search_date and len(search_date) == 2:
            queryset = queryset.filter(sale_order__order_time__range=(search_date[0], search_date[1]))
            orders = orders.filter(order_time__range=(search_date[0], search_date[1]))

        # 本公司信息
        company = Company.objects.first()

        totals_result = orders.aggregate(total_amount=Sum('total_amount'), amount=Sum('amount'), norecv_amount=Sum(F('total_amount') - F('amount')))
        totals_nums = queryset.aggregate(total_nums=Sum('nums'))
        totals_result["total_nums"] = totals_nums.get('total_nums') or 0

        serializer = SaleOrderDetailSerializer(queryset, many=True)

        if export == "1":
            bio = BytesIO()
            writer = pd.ExcelWriter(bio, engine='openpyxl')
            df = pd.DataFrame(serializer.data, columns=['sale_order_code', 'goods_code', 'goods_name', 'goods_specs', 'sale_order_time', 'nums', 'price', 'total_amount'])

            df.rename(columns={'sale_order_code': '订单编号', 'goods_code': '商品编号', 'goods_specs': '规格',
                               'goods_name': '商品名称', 'sale_order_time': '出库时间',
                               'nums': '出库数量', 'price': '单价', 'total_amount': '金额'}, inplace=True)
            series = pd.Series({'订单编号': '合计', '出库数量': totals_result["total_nums"], '金额': totals_result["total_amount"], '已收金额': totals_result["amount"], '未收金额': totals_result["norecv_amount"]})
            series = series.to_frame()
            s2 = pd.DataFrame(series.values.T, columns=series.index)

            df = pd.concat([df, s2], axis=0, ignore_index=True)

            df.to_excel(writer, sheet_name='销售对账单', index=False, float_format='%.2f')

            ws = writer.book.active     # excel sheet

            align = Alignment(horizontal='center', vertical='center', wrap_text=True)
            align_text = Alignment(vertical='center', wrap_text=True)
            # 插入行
            ws.insert_rows(1, 5)
            for i in range(1, 6):
                # 合并
                ws.merge_cells(start_row=i, end_row=i, start_column=1, end_column=ws.max_column)
                # 对齐方式
                ws['A' + str(i)].alignment = align
            # 设置行高
            for row in range(1, ws.max_row + 1):
                ws.row_dimensions[row].height = 25
            # 设置列宽
            for col in range(1, ws.max_column + 1):
                ws.column_dimensions[get_column_letter(col)].width = 18 if col == 1 or col == 3 else 12
            # 设置边框线
            border = Border(left=Side(border_style='thin', color='000000'),
                            right=Side(border_style='thin', color='000000'),
                            top=Side(border_style='thin', color='000000'),
                            bottom=Side(border_style='thin', color='000000'))

            for row in ws.iter_rows(min_row=ws.min_row + 5, max_row=ws.max_row, max_col=ws.max_column):
                for cell in row:
                    cell.border = border
                    cell.alignment = align_text
                    cell.number_format = '0.00'
            # 设置字体
            font = Font(size=16, bold=True)
            font_small = Font(size=9)
            ws['A1'].font = font
            ws['A1'].value = company.name if company else ""
            ws['A2'].font = font_small
            if company:
                ws['A2'].value = '公司地址:{} TEL:{} FAX: {}'.format(company.address, company.telephone, company.fax)
            else:
                ws['A2'].value = ""
            ws['A3'].value = '对账单'
            ws['A3'].font = font

            ws['A4'].font = font_small
            ws['A4'].value = '对账日期:{}至{}'.format(search_date[0], search_date[1])
            if customer:
                ws['A5'].font = font_small
                ws['A5'].value = '客户: {}                联系人:{}           传真:{}'.format(customer.name, customer.linkman, customer.fax)
            writer.save()

            response = HttpResponse(bio.getvalue(), content_type="application/octet-stream")
            customer_name = customer.name if customer else ""
            file_name = '{}至{}{}销售对账单.xlsx'.format(search_date[0], search_date[1], customer_name)
            response['Content-Disposition'] = "attachment;filename={}".format(escape_uri_path(file_name.encode('utf-8')))
            return response
        results = {
            'company': model_to_dict(company) if company else None,
            'customer': model_to_dict(customer) if customer else None,
            'data': serializer.data,
            'sums': totals_result
        }

        return Response(results)


class ReceivableAccountAPIView(APIView):
    """客户应收款"""
    permission_classes = ()

    def get(self, request, *args, **kwargs):
        customer_id = request.query_params.get('customer')
        search_date = self.request.query_params.getlist('search_date[]')

        queryset = SaleOrder.objects.filter(Q(flag=0) | Q(flag=1), status=0)

        if customer_id:
            queryset = queryset.filter(customer_id=int(customer_id))

        if search_date and len(search_date) == 2:
            queryset = queryset.filter(order_time__range=(search_date[0], search_date[1]))

        results = queryset.values('customer_id', customer_name=F('customer__name'))\
            .annotate(total_amounts=Coalesce(Sum('total_amount'), 0.0), amounts=Coalesce(Sum('amount'), 0.0),
                      recv_amounts=Sum(Coalesce(F('total_amount'), 0.0) - Coalesce(F('amount'), 0.0)))\
            .order_by('customer_id', 'customer_name')

        return Response(results)


class ProfitAPIView(APIView):
    """利润表"""
    authentication_classes = ()
    permission_classes = ()

    def get(self, request, *args, **kwargs):
        year = request.query_params.get('year')
        if not year:
            return Response(list())
        # 销售收入合计
        sale_amounts = SaleOrder.objects.filter(Q(flag=0) | Q(flag=1), order_time__year=year)\
            .annotate(year=ExtractYear("order_time"), month=ExtractMonth("order_time")) \
            .values("year", "month") \
            .annotate(sale_total_amounts=Sum("total_amount"))\
            .order_by("year", "month")

        df_sale = pd.DataFrame()
        if sale_amounts.exists():
            df_sale = pd.DataFrame(list(sale_amounts))
            df_sale["date"] = df_sale["year"].astype("str") + "-" + df_sale["month"].apply(lambda x: '{:0>2d}'.format(x)).astype("str")
            df_sale = df_sale.loc[:, ["date", "sale_total_amounts"]]

        # 费用和收入合计
        fund_bills = FundBill.objects.filter(Q(category=0) | Q(category=1), verify=1, order_time__year=year) \
            .annotate(year=ExtractYear("order_time"), month=ExtractMonth("order_time"))\
            .values("year", "month")\
            .annotate(
            cost_amounts=Sum(Case(When(category=0, then=F("money")), default=0, output_field=FloatField())),
            income_amounts=Sum(Case(When(category=1, then=F("money")), default=0, output_field=FloatField()))
                      )\
            .order_by("year", "month")

        df_fund = pd.DataFrame()
        if fund_bills.exists():
            df_fund = pd.DataFrame(list(fund_bills))
            df_fund["date"] = df_fund["year"].astype("str") + "-" + df_fund["month"].apply(lambda x: '{:0>2d}'.format(x)).astype("str")
            df_fund = df_fund.loc[:, ["date", "cost_amounts", "income_amounts"]]

        # 商品成本合计
        goods_costs = SaleOrderDetail.objects.filter(Q(sale_order__flag=0) | Q(sale_order__flag=1),
                                                     sale_order__order_time__year=year) \
            .annotate(year=ExtractYear("sale_order__order_time"), month=ExtractMonth("sale_order__order_time")) \
            .values("year", "month") \
            .annotate(goods_costs_amounts=Sum(Coalesce(F("goods__purchase_price"), 0.0) * Coalesce(F('nums'), 0.0))) \
            .order_by("year", "month")
        # print(goods_costs)
        df_goods_costs = pd.DataFrame()
        if goods_costs.exists():
            df_goods_costs = pd.DataFrame(list(goods_costs))
            df_goods_costs["date"] = df_goods_costs["year"].astype("str") + "-" + df_goods_costs["month"].apply(lambda x: '{:0>2d}'.format(x)).astype("str")
            df_goods_costs = df_goods_costs.loc[:, ["date", "goods_costs_amounts"]]

        # 优惠金额
        discount_costs = SaleReceipt.objects.filter(recv_time__year=year)\
            .annotate(year=ExtractYear("recv_time"), month=ExtractMonth("recv_time"))\
            .values("year", "month")\
            .annotate(discount_amounts=Sum("discount_amount")) \
            .order_by("year", "month")

        df_discount = pd.DataFrame()
        if discount_costs.exists():
            df_discount = pd.DataFrame(list(discount_costs))
            df_discount["date"] = df_discount["year"].astype("str") + "-" + df_discount["month"].apply(lambda x: '{:0>2d}'.format(x)).astype("str")
            df_discount = df_discount.loc[:, ["date", "discount_amounts"]]

        cur_year = datetime.datetime.now().year
        month = datetime.datetime.now().month
        periods = month if int(year) == cur_year else 12

        date_index = pd.period_range(start=f'{year}-1', periods=periods, freq='M', name='date')
        df = pd.DataFrame(list(date_index.astype('str')), columns=["date"])

        if not df_sale.empty:
            df = pd.merge(df, df_sale, on="date", how="left")
        else:
            df["sale_total_amounts"] = 0.0

        if not df_fund.empty:
            df = pd.merge(df, df_fund, on="date", how="left")
        else:
            df["cost_amounts"] = 0.0
            df["income_amounts"] = 0.0

        if not df_goods_costs.empty:
            df = pd.merge(df, df_goods_costs, on="date", how="left")
        else:
            df["goods_costs_amounts"] = 0.0

        if not df_discount.empty:
            df = pd.merge(df, df_discount, on="date", how="left")
        else:
            df["discount_amounts"] = 0.0

        df.fillna(0, inplace=True)
        df["profit_amounts"] = df["sale_total_amounts"] + df["income_amounts"] - df["cost_amounts"] - df["goods_costs_amounts"] - df["discount_amounts"]
        data = df.to_json(orient="records")
        dict_data = json.loads(data)
        return Response(dict_data)

